#!"/Applications/XAMPP/xamppfiles/bin/perl"

package DataBaseManager;
use lib '/Library/Perl/5.12/';
use DBI;
use models::User;
use models::Supplement;
use models::Pizza;
use models::Order;
use Data::Dumper;
use JSON;
sub new
{
    my $class = shift;
    my $self = { _dbh => shift };
    bless $self, $class;    
    return $self;
}

sub connect
{
	 my( $self ) = @_;
	 $self->{_dbh} = DBI->connect('DBI:mysql:MyPizza', 'root', '')
	 || die "Could not connect to database: $DBI::errstr";
	 
}

sub disconnect
{
    my( $self ) = @_;
	$self->{_dbh}->disconnect()|| print "Could not disconnect to database: $DBI::errstr";
}

sub getDbh {
    my( $self ) = @_;
    return $self->{_dbh};
}


sub selectUser
{
    my( $self,$id) = @_;
	my $sth = $self->{_dbh}->prepare(qq{SELECT * FROM users where id= ?}) || die("select error");
	$sth->execute($id) || die ("error");
	my $hash =  $sth->fetchrow_hashref;
	my $user = new User($hash->{username},$hash->{password},$hash->{address},$hash->{phone},$hash->{ordersAddress});
	$user->setId($hash->{id});
	return $user;
}


# Params: object:User
sub register
{
	my($self, $user) = @_;
		
	my $username = $user->getUsername();
	my $password = $user->getPassword();
	my $address = $user->getAddress();
	my $phone = $user->getPhone();
	my $ordersAddress = $user->getOrderAddress();
    my $sth = $self->{_dbh}->prepare(qq{
    	INSERT INTO users (username, address, phone, password, ordersAddress) VALUES (?, ?, ?, ?, ?)
    }) 
    	|| die "Could not insert to database: $DBI::errstr";
    
    $sth->execute($username, $address, $phone, $password, $ordersAddress)|| die "Could not insert to database: $DBI::errstr";
	$id = $sth->{mysql_insertid};
	return $id;
}

sub login
{
	my( $self , $username, $password) = @_;
#    my $username = $user->getUsername();
#    my $password = $user->getPassword();
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM users WHERE username = ? AND password = ?
	}) || die "Could not select user from database!";
	$sth->execute($username, $password);
	my $result = $sth->fetchrow_hashref();
	if($result == undef)
	{
		 $result = {"error","Incorrect login!"};
	}
	return $result;
}

sub getUserById
{
	my( $self, $id ) = @_;
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM users WHERE id = ?
	}) || die "Could not select user from database!";
	$sth->execute($id);
	my $result = $sth->fetchrow_hashref();
	return $result;
}

sub updateUser
{
	my( $self, $user ) = @_;
    my $id = $user->getId();
    my $username = $user->getUsername();
	my $password = $user->getPassword();
	my $address = $user->getAddress();
	my $phone = $user->getPhone();
	my $ordersAddress = $user->getOrderAddress();
	my $sth = $self->{_dbh}->prepare(qq{
		UPDATE users SET username=?, password=?, address=?, phone=?, ordersAddress=? WHERE id = ?
	}) || die "Could not user user to database!";
	my $result = $sth->execute($username, $password, $address, $phone, $ordersAddress, $id)|| die "Could not user user to database!";
	return int($result);
}

sub getAllSupplements
{
	my ($self) = @_;
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM pizzaSupplements ORDER BY id ASC
	}) || die "Could not select supplements from database!";
	$sth->execute()|| die "Could not die !";
	my %supplements;
	my $i = 0;
#	my $tempSupplement;
	while( my $hash = $sth->fetchrow_hashref())
	{
#		$supplement = new Supplement($hash->{id},$hash->{type},$hash->{name},$hash->{pricePerUnit},$hash->{unitType},0);
		$supplements{$i} = $hash;
		$i = $i + 1;
	}
#	print Dumper(@supplements);
	return %supplements;
}

sub setPizzaSupplements
{
	my ($self, $pizzaId, $supplementsArray ) = @_;
	my $i = 0;
	my $sqlQuery = "INSERT INTO pizzaSupplementsRelation (`pizzaId`, `supplementId`,`quantity`) VALUES";
#	print $supplementsArray;
	my @supplements = decode_json($supplementsArray);
#	print Dumper(@supplements);
	foreach my $element (@supplements)
	{
#		print "element".Dumper($element->{'2'});
		my %arr = %{$element};
		foreach my $supp (sort keys %arr) 
		{
#			print Dumper($arr{$supp})."asd";
	 	 	my $suplId = $arr{$supp}{'id'}+1;
			my $quantity = $arr{$supp}{'amountOrdered'};
			$sqlQuery .= "," if ($i!=0);
			$sqlQuery .= "($pizzaId, $suplId, $quantity)";
			$i++;
		}
	}
#	print "<br>".Dumper($sqlQuery);
	$self->{_dbh}->do($sqlQuery) || die "Could not insert supplements to database: $DBI::errstr";
	
}

sub getPizzaSupplementsById
{
	my ($self, $id ) = @_;
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM pizzaSupplementsRelation psr 
		JOIN pizzaSupplements ps
		ON ps.id = psr.supplementId
	WHERE psr.pizzaId = ?
	}) || die "Could not select supplements from database!";
	$sth->execute($id);
	my $result = $sth->fetchrow_hashref;
	my %supplements;
	my $tempSupplement;
	my $i = 0;
	while( my $hash = $sth->fetchrow_hashref())
	{
#		$tempSupplement = new Supplement($hash->{id},$hash->{type},$hash->{name},$hash->{pricePerUnit},$hash->{unitType},0);
		$supplements{$i} = $hash;
		$i = $i + 1;
	}
	return %supplements;
}

sub getUserPizzasByUserId
{
	my ($self, $userId ) = @_;
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM pizzas p 
	WHERE p.userId = ?
	}) || die "Could not select user pizzas from database!";
	$sth->execute($userId);
	my %pizzas;
	my $tempSupplement;
	my $i = 0;
	while( my $hash = $sth->fetchrow_hashref())
	{
		$pizzas{$i} = $hash;
		$i = $i + 1;
	}
	return %pizzas;
}

sub setPizza
{
	my( $self,$pizza ) = @_;
  	my $name = $pizza->getName;
  	my $userId = $pizza->getUserId;
  	my $size = $pizza->getSize;
  	my $price = $pizza->getPrice;
	my $sth = $self->{_dbh}->prepare(qq{
		INSERT INTO pizzas (`name`, `userId`, `size`, `price`)
			VALUES(?, ?, ?, ?)
	}) || die "Could not insert pizza to database!";
	$sth->execute($name, $userId, $size, $price) || die("died");
	$id = $sth->{mysql_insertid};
	return $id;
}

sub getUserOrders
{
	my ($self,$userId) = @_;
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM orders o 
	WHERE o.userId = ?
	}) || die "Could not select user orders from database!";
	$sth->execute($userId);
	my %orders;
	my $i = 0;
	while( my $hash = $sth->fetchrow_hashref())
	{
		$orders{$i} = $hash;
		$i = $i + 1;
	}
	return %orders;
}

sub setOrder
{
	my( $self , $order ) = @_;
  	my $userId = $order->getUserId;
  	my $pizzaId = $order->getPizzaId;
  	my $status = $order->getStatus;
  	my $shippingPrice = $order->getShippingPrice;
  	my $orderAddress = $order->getOrderAddress;
  	my $shippingTime = $order->getShippingTime;
	my $sth = $self->{_dbh}->prepare(qq{
		INSERT INTO orders (`userId`, `pizzaId`, `status`, `shippingPrice`,`shippingTime`,`orderAddress`,`timestamp`)
			VALUES(?, ?, ?, ?, ?, ?,?)
	}) || die "Could not insert order to database!";
	$sth->execute($order, $pizzaId, $status, $shippingPrice, $shippingTime,$orderAddress,time);
	$id = $sth->{mysql_insertid};
	return $id;
}

sub updateOrder
{
	my( $self , $orderId ) = @_;
	my $sth = $self->{_dbh}->prepare(qq{
		UPDATE orders  SET `status` = "packaging"
			WHERE `id` = ?
	}) || die "Could not insert order to database!";
	$sth->execute($orderId);
	return $orderId;
}

sub getAllOrders
{
	my( $self ) = @_;
	my $time = time;
	my $sth = $self->{_dbh}->prepare(qq{
		SELECT * FROM orders WHERE (`timestamp` > ? - 1800 AND `status` = "baking") AND `timestamp` < ? - 600
	}) || die "Could not insert order to database!";
	$sth->execute($time, $time);
	my %orders;
	my $i = 0;
	while( my $hash = $sth->fetchrow_hashref())
	{
		$orders{$i} = $hash;
		$i = $i + 1;
	}
	return %orders;
}

1;